/* Change logs
 * Copyright (C) 2011 Frozen LaB
 * 
 * Description: database adapter which implement CRUD
 * 
 * VERS  DATE        AUTHOR                 REMARKS
 * ====  =========== ====================   ==============================================
 * 1.0   22-NOV-2011 winson tan             Base Creation
 * 
 */

package com.frozenlab.android.wheretoeatai;

import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class FoodPlaceDbHelper {

	private static final String DATABASE_NAME = "wheretoeatai.db";
    private static final String TABLE_NAME = "t_food_places";
    
    /* Database table keys mapping */
    public static final String KEY_ROWID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_IS_BREAKFAST = "is_breakfast";
	public static final String KEY_IS_LUNCH = "is_lunch";  
	public static final String KEY_IS_DINNER = "is_dinner";  
	public static final String KEY_IS_SUPPER = "is_supper";  
	public static final String KEY_IS_REFRESHMENT = "is_refreshment";  
	public static final String KEY_IS_OTHERS = "is_others";  
	public static final String KEY_PRICE_RANGE = "price_range"; 
    
    private static final String TAG = "FoodPlaceDbHelper";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table " + TABLE_NAME + "(_id integer primary key autoincrement, " +
        " name text not null," +
        " is_breakfast integer," +
        " is_lunch integer, " +
        " is_dinner integer," +
        " is_supper integer," +
        " is_refreshment integer," +
        " is_others integer," +
        " price_range text);";


    private DatabaseHelper dbHelper;
    private SQLiteDatabase mDb;
    private final Context mCtx;

    private static final Map<Integer, String> categoryMap = new HashMap<Integer, String>();
    static {
    	categoryMap.put(R.string.Breakfast, KEY_IS_BREAKFAST);
    	categoryMap.put(R.string.Lunch, KEY_IS_LUNCH);
    	categoryMap.put(R.string.Dinner, KEY_IS_DINNER);
    	categoryMap.put(R.string.Supper, KEY_IS_SUPPER);
    	categoryMap.put(R.string.Refreshment, KEY_IS_REFRESHMENT);
    	categoryMap.put(R.string.Others, KEY_IS_OTHERS);
    }
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be opened/created
     * 
     * @param ctx the Context within which to work
     */
    public FoodPlaceDbHelper(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public FoodPlaceDbHelper open() throws SQLException {
    	dbHelper = new DatabaseHelper(mCtx);
        mDb = dbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
    	dbHelper.close();
    }


    /**
     * Create a new record in table. If the record is
     * successfully created return the new rowId for that record, otherwise return
     * a -1 to indicate failure.
     * 
     * @param name the name of the food place
     * @param isBreakfast the flag indicate is breakfast
     * @param isLunch the flag indicate is lunch
     * @param isDinner the flag indicate is dinner
     * @param isSupper the flag indicate is supper
     * @param isRefreshment the flag indicate is refreshment
     * @param isOthers the flag indicate is others
     * @param priceRange the price range of food place
     * @return rowId or -1 if failed
     */
    public long createRecord(String name, String isBreakfast, String isLunch, String isDinner, String isSupper, 
    		String isRefreshment, String isOthers, String priceRange) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_IS_BREAKFAST, isBreakfast);
        initialValues.put(KEY_IS_LUNCH, isLunch);
        initialValues.put(KEY_IS_DINNER, isDinner);
        initialValues.put(KEY_IS_SUPPER, isSupper);
        initialValues.put(KEY_IS_REFRESHMENT, isRefreshment);
        initialValues.put(KEY_IS_OTHERS, isOthers);
        initialValues.put(KEY_PRICE_RANGE, priceRange);

        return mDb.insert(TABLE_NAME, null, initialValues);
    }

    /**
     * Delete the record with the given rowId
     * 
     * @param rowId id of record to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteRecord(long rowId) {

        return mDb.delete(TABLE_NAME, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all records in the database
     * 
     * @return Cursor over all records
     */
    public Cursor readAllRecords() {

        return mDb.query(TABLE_NAME, new String[] {
        		KEY_ROWID, 
        		KEY_NAME,
                KEY_IS_BREAKFAST,
                KEY_IS_LUNCH,
                KEY_IS_DINNER,
                KEY_IS_SUPPER,
                KEY_IS_REFRESHMENT,
                KEY_IS_OTHERS,
                KEY_PRICE_RANGE}, null, null, null, null, KEY_NAME);
    }

    /**
     * Return a Cursor positioned at the record that matches the given rowId
     * 
     * @param rowId id of record to retrieve
     * @return Cursor positioned to matching record, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor readRecord(long rowId) throws SQLException {

        Cursor mCursor =
            mDb.query(true, TABLE_NAME, new String[] {
            		KEY_ROWID, 
            		KEY_NAME,
                    KEY_IS_BREAKFAST,
                    KEY_IS_LUNCH,
                    KEY_IS_DINNER,
                    KEY_IS_SUPPER,
                    KEY_IS_REFRESHMENT,
                    KEY_IS_OTHERS,
                    KEY_PRICE_RANGE}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the record using the details provided. The record to be updated is
     * specified using the rowId, and it is altered to use the table columns 
     * values passed in
     * 
     * @param rowId id of record to update
     * @param name the name of the food place to update
     * @param isBreakfast the flag to update is breakfast
     * @param isLunch the flag to update is lunch
     * @param isDinner the flag to update is dinner
     * @param isSupper the flag to update is supper
     * @param isRefreshment the flag to update is refreshment
     * @param isOthers the flag to update is others
     * @param priceRange the value to update price range of food place
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateRecord(long rowId, String name, String isBreakfast, String isLunch, String isDinner, String isSupper, 
    		String isRefreshment, String isOthers, String priceRange) {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);
        args.put(KEY_IS_BREAKFAST, isBreakfast);
        args.put(KEY_IS_LUNCH, isLunch);
        args.put(KEY_IS_DINNER, isDinner);
        args.put(KEY_IS_SUPPER, isSupper);
        args.put(KEY_IS_REFRESHMENT, isRefreshment);
        args.put(KEY_IS_OTHERS, isOthers);
        args.put(KEY_PRICE_RANGE, priceRange);

        return mDb.update(TABLE_NAME, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Return a Cursor positioned at the record that matches the given rowId
     * 
     * @param rowId id of record to retrieve
     * @return Cursor positioned to matching record, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor selectFoodPlaceByCategory(int resId) throws SQLException {

    	String columnName = FoodPlaceDbHelper.categoryMap.get(resId);
        Cursor mCursor =
            mDb.query(true, TABLE_NAME, new String[] {
            		KEY_ROWID, 
            		KEY_NAME,
                    KEY_PRICE_RANGE}, columnName + "=1", null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
}
